In general, the present invention relates to computer software, and in particular, to a system and method for processing movement/delta metrics utilizing metric aggregation rules.
Generally described, metric data may be collected and presented in a variety of manners. For example, metric data may include information related to the amount money stored in bank account, the number of products shipped by a manufacturer, the amount of inventory maintained by a corporation, and the like. Often, metric data is organized according to one or more keys that identify the source of the data. The identification keys can include regular keys that identify a characteristic of the metric data, such as location identifier (e.g., Northwest), a product identifier (e.g., a product name). The identification keys can also include time keys that identify a particular time period from which the metric data was collected, such as time, date, month identifier and fiscal quarter identifier.
As the metric data is collected, the raw data may be stored and maintained in one or more databases for retrieval and processing. The databases can include data files, relational databases, and any other data storage component. FIG. 1 is a block diagram illustrative of a database record 48 including metric data. As illustrated in FIG. 1, the database record 48 includes one or more identification keys, such as a xe2x80x9cTIMExe2x80x9d key 50A, a xe2x80x9cPRODUCTxe2x80x9d key 50B, and a xe2x80x9cREGIONxe2x80x9d key 50C. The database record 48 also includes metric data, such as a xe2x80x9cDOLLARSxe2x80x9d metric 52A and a xe2x80x9cQUANTITYxe2x80x9d metric 52B. Accordingly, a processing system can access the raw data within the database records to perform various processing tasks involving multiple database records.
One typical metric data processing task involves the use of aggregation rules to present a user with metric data trends. For example, a manufacturer may wish to obtain processed data displaying the quantity of product that was shipped out on a monthly basis. Accordingly, the processing system would obtain the raw metric data from the data sources and group the data by a product identifier key (if there is more than one product) and a time identifier key to get the necessary data.
The ability for a processing system to process raw metric data depends on the type of metric data maintained within a data store. In one embodiment, the metric data includes independent, finite numbers that illustrate a sampling of a quantity/value at the particular instance of time, generally referred to as regular metrics. Examples of regular metrics can include the total dollar amount or quantity of sales/purchases over a defined period of time, and the like. Accordingly, regular metric data provide a xe2x80x9csnapshotxe2x80x9d of a total of amount of an item being counted.
FIG. 2 is a flow diagram illustrative of a regular metric processing routine 200 utilized by a processing system, such as an accounting processing system, to process raw regular metric data from a data source. At block 202, the system obtains the raw data store, such as a data file, and applies one or more filter keys. It is assumed that each database records includes one or more identification keys, such as the xe2x80x9cTIME,xe2x80x9d xe2x80x9cPRODUCT,xe2x80x9d and xe2x80x9cREGIONxe2x80x9d fields illustrated in FIG. 1. Accordingly, the processing system selects which identification key and what values of the key correlate to the desired results. In an illustrative embodiment, assume that the processing system must generate sales dollars for the first and second fiscal quarters of the year 2000 for product X. Based on the above identifiers, the processing system would filter the raw metric data to include all records that had xe2x80x9cproduct Xxe2x80x9d in the product key 50B and that would be included in the first and second fiscal quarters of the year 2000 (e.g., January, February, and March 2000 and April, May and June 2000).
At block 204, the processing system applies aggregation rules on the regular metric data matching the filter key values. Once the processing system has identified the raw metric data that corresponds to the desired results, the processing system aggregates the matching metric data that will be necessary to get the final output. With reference to the previous example, the processing system would aggregate the January, February, and March data to obtain the first fiscal quarter number and the April, May and June data to obtain the second fiscal quarter number. At block 206, the processing system calculates sub-totals for the aggregated metric data.
At block 208, the processing system places the aggregated metric data and sub-total data into a multi-dimensional grid for presentation. With reference again to the illustrative example, the processing system would place the totals for each month (January-June) into a grid and the sub-totals for the first and second fiscal quarters. At block 210, the routine 200 terminates.
For a variety of reasons, metric data may not always be maintained in terms of regular metrics. Instead, the raw metric data may be stored, and subsequently needs to be processed, as movement/delta metric data. One skilled in the relevant art will appreciate that movement/delta metric data is a finite number that increases/decreases a running total calculated from previous movement/delta metric data. Examples of movement/delta metric data include inventory movement, headcount change, asset value change, open order amount changes, and the like. For example, a bank generally keeps track of asset value changes by accounting for deposits as an addition to the account (e.g., +$50) and withdrawals as a subtraction to the account (e.g., xe2x88x92$25). However, database records containing movement/delta metric data do not include a xe2x80x9csnapshotxe2x80x9d of the total of the asset value, but rather adjust a running total.
Conventional aggregation rules processing system are deficient in attempting to process movement/delta metric data. In one aspect, the conventional aggregation rule processing method is deficient because the movement/delta metric is dependent on previous data, the conventional processing method does not take into account database records that do not match the filter keys, but contain data necessary to calculate the matching record total. For example, assume the processing system desired to calculate the total amount of inventory for February based on a record indicating a xe2x80x9c+25xe2x80x9d total for February and a year total of 100 in December. To calculate the proper result, the processing system would have to take into account the movement/delta metric for January and December, even though these records would not be included in the desired results as determined by conventional aggregation rules.
In another aspect, the conventional processing method is further deficient in the not properly processing movement/delta metric data including null, or zero, values. For example, assume that the processing system desired to calculate an open order balance for the first fiscal quarter with movement/delta metric data indicating a xe2x80x9c+10,xe2x80x9d null (no activity) and xe2x80x9cxe2x88x922xe2x80x9d values for the first three months. In a conventional aggregation rule processing method, the processing system would treat the order balance for the second month as null (no activity), instead of the correct answer as xe2x80x9cno change.xe2x80x9d Accordingly, the system would have to carry over the balance from the previous month, which conventional processing systems are unable to do.
Some systems are capable of processing movement/delta metric data. However, these systems are limited in the type of processing that can be accomplished. For example, some systems require the movement/delta metric data to be stored sequentially according to a time identification key to allow the system to take in account records immediately before a record of interest. Other systems are limited in not allowing arbitrary beginning and ending periods for data processing or arbitrary inclusion and exclusion of keys. For example, some systems are limited to processing movement/delta metric solely on a monthly basis with a single point of xe2x80x9cending balancexe2x80x9d (such as a monthly banking statement).
Thus, there is a need for a metric data processing system that extends regular metric aggregation rules to process movement/delta metric in a variety of manners over data records stored in a variety of formats.
A system and method for processing movement/delta metric data are provided. A processing system obtains one or more data records including movement/delta metric data. The processing system filters the data records according to a regular identification key filters and time key filters and generates a match table and a time-filtered records table. The match table contains data records matching both filters and the time-filtered records table contains data records matching the regular identification key filters but not matching the time key filters. The processing system merges the match table with the time-filtered records table to generate an aggregated multi-dimensional grid display.
In accordance with an aspect of the present invention, a method for processing data records including movement/delta metric data in an analytical processing environment is provided. A processing system obtains a database containing the data records and generates a match table and a time-filtered records table from the data records. The processing system merges the time-filtered records table with one or more records in the match table and presents the merged match table records in a multi-dimensional table.
In accordance with another aspect of the present invention, a computer-readable medium having computer-executable modules for processing data records including movement/delta metric data in an analytical processing environment is provided. The computer-readable medium includes a match table module including a table of data records from a data source matching a regular identification key filter and a time key filter. The match table module is operable to obtain a total value for movement/delta metric data. The computer-readable medium also includes a time-filtered records table module including a table of data records matching the regular identification filter, but not matching the time key filter. The time-filtered records table module is operable to provide additional movement/delta metric data corresponding of the calculation the final xe2x80x9cSUM-TO-DATExe2x80x9d value for the movement/delta metric data.
In accordance with a further aspect of the present invention, a system for processing movement/delta metric data is provided. The system includes a data source including one or more data records that contain at least one field having movement/delta metric data. The system further includes means for processing the movement/delta metric field data according to an inputted set of criteria. Finally, the system includes means for displaying the processed movement/delta metric field data.